/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.ac.iie.ulss.deploybackup.main;

import cn.ac.iie.ulss.deploybackup.commons.RuntimeEnv;
import cn.ac.iie.ulss.deploybackup.config.Configuration;
import cn.ac.iie.ulss.deploybackup.util.Meta;
import org.apache.log4j.Logger;
import org.apache.log4j.PropertyConfigurator;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Map;
import java.util.Properties;
import java.util.Set;
import java.io.File;
import jxl.CellType;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.format.Alignment;
import jxl.write.DateFormat;
import jxl.write.Label;
import jxl.write.NumberFormat;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 *
 * @author Li Mingyang
 */
public class Deploybackup {

    static Logger logger = null;

    static {
        PropertyConfigurator.configure("log4j.properties");
        logger = Logger.getLogger(Deploybackup.class.getName());
    }

    public static void showUsage() {
        System.out.println("Usage:java -jar ");
    }

    public static void main(String[] args) {
        // TODO code application logic here
        try {
            init();
            startup();
        } catch (Exception ex) {
            logger.error("starting deploybackup is failed for " + ex.getMessage(), ex);
        }
        System.exit(0);
    }

    private static void init() throws Exception {
        String configurationFileName = "ulss-data-deploybackup.properties";
        logger.info("getting configuration from configuration file " + configurationFileName);
        Configuration conf = Configuration.getConfiguration(configurationFileName);
        if (conf == null) {
            throw new Exception("reading " + configurationFileName + " is failed");
        }
        logger.info("initializng runtime enviroment...");
        if (!RuntimeEnv.initialize(conf)) {
            throw new Exception("initializng runtime enviroment is failed");
        }
        logger.info("initialize runtime enviroment successfully");
    }

    private static void startup() throws Exception {
        // 打开文件  
        long namo = System.currentTimeMillis();
        String fileName = "IIE_DEPLOYMENT_".concat(String.valueOf(namo)).concat(".xls");
        WritableWorkbook book = Workbook.createWorkbook(new File(fileName));
        Map<String, Meta> sheetMetaMap = (Map<String, Meta>) RuntimeEnv.getParam(RuntimeEnv.SHEETMETAMAP);
        Set<String> sheets = sheetMetaMap.keySet();
        int page = 0;
        for (String sheetString : sheets) {
            logger.info("begin to generate " + sheetString);
            WritableSheet sheet = book.createSheet(sheetString, page++);
            //列搞完了
            Label label = new Label(0, 0, "节点IP", getDataCellFormat(CellType.LABEL));
            sheet.addCell(label);
            label = new Label(1, 0, "前端接收", getDataCellFormat(CellType.LABEL));
            sheet.addCell(label);
            label = new Label(2, 0, "重分布", getDataCellFormat(CellType.LABEL));
            sheet.addCell(label);
            label = new Label(3, 0, "比对拆分", getDataCellFormat(CellType.LABEL));
            sheet.addCell(label);
            label = new Label(4, 0, "仓库加载", getDataCellFormat(CellType.LABEL));
            sheet.addCell(label);
            label = new Label(5, 0, "持久化", getDataCellFormat(CellType.LABEL));
            sheet.addCell(label);
            label = new Label(6, 0, "MAPPER", getDataCellFormat(CellType.LABEL));
            sheet.addCell(label);
            label = new Label(7, 0, "REDUCER", getDataCellFormat(CellType.LABEL));
            sheet.addCell(label);
            label = new Label(8, 0, "REDIS", getDataCellFormat(CellType.LABEL));
            sheet.addCell(label);
            label = new Label(9, 0, "RMN", getDataCellFormat(CellType.LABEL));
            sheet.addCell(label);
            label = new Label(10, 0, "RMB", getDataCellFormat(CellType.LABEL));
            sheet.addCell(label);
            label = new Label(11, 0, "ZK", getDataCellFormat(CellType.LABEL));
            sheet.addCell(label);
            label = new Label(12, 0, "STAT_长安", getDataCellFormat(CellType.LABEL));
            sheet.addCell(label);
            label = new Label(13, 0, "STAT_任子行", getDataCellFormat(CellType.LABEL));
            sheet.addCell(label);
            label = new Label(14, 0, "IDX_RECOVER", getDataCellFormat(CellType.LABEL));
            sheet.addCell(label);
            //开始查内容
            Meta meta = sheetMetaMap.get(sheetString);
            String sql = "select * from ip_service order by cast(right(ip,charindex('.',reverse(ip))-1)as int)";
            ResultSet rs = null;
            Connection conn = null;
            try {
                logger.info("try to create a new connection to metastore");
                Class.forName(meta.getMetastoreDriver());
                Properties connPro = new Properties();
                connPro.setProperty("user", meta.getMetastoreUser());
                connPro.setProperty("password", meta.getMetastorePwd());
                conn = DriverManager.getConnection(meta.getMetastoreConnStr(), connPro);
                logger.info("create a new connection to metastore successfully ");
            } catch (ClassNotFoundException cnfex) {
                logger.error("load driver for metastore unsuccessfully for " + cnfex.getMessage(), cnfex);
                conn = null;
            } catch (Exception ex) {
                try {
                    conn.close();
                } catch (Exception ex1) {
                    logger.error(ex1.getMessage(), ex1);
                } finally {
                    conn = null;
                }
                logger.warn("create connection to metastore unsuccessfully for " + ex.getMessage(), ex);
            }
            if (conn == null) {
                logger.error("create connection to metastore unsuccessfully finally");
                throw new Exception("create connection to metastore unsuccessfully");
            }
            try {
                Statement stmt = conn.createStatement();
                rs = stmt.executeQuery(sql);
                int line = 1;
                while (rs.next()) {
                    int column = 0;
                    Label datalabel;
                    if (rs.getString("ip") == null || "".equals(rs.getString("ip")) || rs.getString("ip").contains("null")) {
                        datalabel = new Label(column++, line, "");
                    } else {
                        datalabel = new Label(column++, line, rs.getString("ip").trim(), getDataCellFormat(CellType.STRING_FORMULA));
                    }
                    sheet.addCell(datalabel);
                    if (rs.getString("dd") == null || "".equals(rs.getString("dd")) || rs.getString("dd").contains("null")) {
                        datalabel = new Label(column++, line, "");
                    } else {
                        datalabel = new Label(column++, line, rs.getString("dd").trim(), getDataCellFormat(CellType.STRING_FORMULA));
                    }
                    sheet.addCell(datalabel);
                    if (rs.getString("matcher") == null || "".equals(rs.getString("matcher")) || rs.getString("matcher").contains("null")) {
                        datalabel = new Label(column++, line, "");
                    } else {
                        datalabel = new Label(column++, line, rs.getString("matcher").trim(), getDataCellFormat(CellType.STRING_FORMULA));
                    }
                    sheet.addCell(datalabel);
                    if (rs.getString("se2dw") == null || "".equals(rs.getString("se2dw")) || rs.getString("se2dw").contains("null")) {
                        datalabel = new Label(column++, line, "");
                    } else {
                        datalabel = new Label(column++, line, rs.getString("se2dw").trim(), getDataCellFormat(CellType.STRING_FORMULA));
                    }
                    sheet.addCell(datalabel);
                    if (rs.getString("idx") == null || "".equals(rs.getString("idx")) || rs.getString("idx").contains("null")) {
                        datalabel = new Label(column++, line, "");
                    } else {
                        datalabel = new Label(column++, line, rs.getString("idx").trim(), getDataCellFormat(CellType.STRING_FORMULA));
                    }
                    sheet.addCell(datalabel);
                    if (rs.getString("mapper") == null || "".equals(rs.getString("mapper")) || rs.getString("mapper").contains("null")) {
                        datalabel = new Label(column++, line, "");
                    } else {
                        datalabel = new Label(column++, line, rs.getString("mapper").trim(), getDataCellFormat(CellType.STRING_FORMULA));
                    }
                    sheet.addCell(datalabel);
                    if (rs.getString("reducer") == null || "".equals(rs.getString("reducer")) || rs.getString("reducer").contains("null")) {
                        datalabel = new Label(column++, line, "");
                    } else {
                        datalabel = new Label(column++, line, rs.getString("reducer").trim(), getDataCellFormat(CellType.STRING_FORMULA));
                    }
                    sheet.addCell(datalabel);
                    if (rs.getString("redis") == null || "".equals(rs.getString("redis")) || rs.getString("redis").contains("null")) {
                        datalabel = new Label(column++, line, "");
                    } else {
                        datalabel = new Label(column++, line, rs.getString("redis").trim(), getDataCellFormat(CellType.STRING_FORMULA));
                    }
                    sheet.addCell(datalabel);
                    if (rs.getString("rmn") == null || "".equals(rs.getString("rmn")) || rs.getString("rmn").contains("null")) {
                        datalabel = new Label(column++, line, "");
                    } else {
                        datalabel = new Label(column++, line, rs.getString("rmn").trim(), getDataCellFormat(CellType.STRING_FORMULA));
                    }
                    sheet.addCell(datalabel);
                    if (rs.getString("rmb") == null || "".equals(rs.getString("rmb")) || rs.getString("rmb").contains("null")) {
                        datalabel = new Label(column++, line, "");
                    } else {
                        datalabel = new Label(column++, line, rs.getString("rmb").trim(), getDataCellFormat(CellType.STRING_FORMULA));
                    }
                    sheet.addCell(datalabel);
                    if (rs.getString("zk") == null || "".equals(rs.getString("zk")) || rs.getString("zk").contains("null")) {
                        datalabel = new Label(column++, line, "");
                    } else {
                        datalabel = new Label(column++, line, rs.getString("zk").trim(), getDataCellFormat(CellType.STRING_FORMULA));
                    }
                    sheet.addCell(datalabel);
                    if (rs.getString("stat_ca") == null || "".equals(rs.getString("stat_ca")) || rs.getString("stat_ca").contains("null")) {
                        datalabel = new Label(column++, line, "");
                    } else {
                        datalabel = new Label(column++, line, rs.getString("stat_ca").trim(), getDataCellFormat(CellType.STRING_FORMULA));
                    }
                    sheet.addCell(datalabel);
                    if (rs.getString("stat_rzx") == null || "".equals(rs.getString("stat_rzx")) || rs.getString("stat_rzx").contains("null")) {
                        datalabel = new Label(column++, line, "");
                    } else {
                        datalabel = new Label(column++, line, rs.getString("stat_rzx").trim(), getDataCellFormat(CellType.STRING_FORMULA));
                    }
                    sheet.addCell(datalabel);
                    if (rs.getString("idx_recover") == null || "".equals(rs.getString("idx_recover")) || rs.getString("idx_recover").contains("null")) {
                        datalabel = new Label(column++, line, "");
                    } else {
                        datalabel = new Label(column++, line, rs.getString("idx_recover").trim(), getDataCellFormat(CellType.STRING_FORMULA));
                    }
                    sheet.addCell(datalabel);
                    line++;
                }
                rs.close();
                conn.close();
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
            }
            logger.info(sheetString + " is done");
        }
        // 写入数据并关闭文件  
        book.write();
        book.close();
    }

    public static WritableCellFormat getDataCellFormat(CellType type) {
        WritableCellFormat wcf = null;
        try {
            // 字体样式  
            if (type == CellType.NUMBER || type == CellType.NUMBER_FORMULA) {// 数字  
                NumberFormat nf = new NumberFormat("#.00");
                wcf = new WritableCellFormat(nf);
            } else if (type == CellType.DATE) {// 日期  
                DateFormat df = new DateFormat("yyyy-MM-dd hh:mm:ss");
                wcf = new WritableCellFormat(df);
            } else if (type == CellType.STRING_FORMULA) {// 日期  
                WritableFont wf = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD, false);
                // 字体颜色  
                wf.setColour(Colour.BLACK);
                wcf = new WritableCellFormat(wf);
                // 对齐方式  
                wcf.setAlignment(Alignment.CENTRE);
                wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
                // 设置背景色  
                wcf.setBackground(Colour.GREEN);
                // 自动换行  
                wcf.setWrap(true);
            } else {
                WritableFont wf = new WritableFont(WritableFont.TIMES, 15, WritableFont.BOLD, false);
                wf.setColour(Colour.BLACK);
                wcf = new WritableCellFormat(wf);
                wcf.setAlignment(Alignment.CENTRE);
                wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
                wcf.setWrap(true);
            }
            // 对齐方式  
//            wcf.setAlignment(Alignment.CENTRE);  
//            wcf.setVerticalAlignment(VerticalAlignment.CENTRE);  
            // 设置上边框  
//            wcf.setBorder(Border.TOP, BorderLineStyle.THIN);  
            // 设置下边框  
//            wcf.setBorder(Border.BOTTOM, BorderLineStyle.THIN);  
            // 设置左边框  
//            wcf.setBorder(Border.LEFT, BorderLineStyle.THIN);  
            // 设置右边框  
//            wcf.setBorder(Border.RIGHT, BorderLineStyle.THIN);  
            // 设置背景色  
//            wcf.setBackground(Colour.GREEN);  
            // 自动换行  
//            wcf.setWrap(true);  
        } catch (WriteException e) {
            e.printStackTrace();
        }
        return wcf;
    }
}
